use crate::error::{new_err, AppErr, SqlxErr};
use serde::Deserialize;
use sqlx::Executor;
use sqlx::Row;

use super::get_pool;

const CREATE_SQL: &'static str = r#"
CREATE TABLE IF NOT EXISTS tb_file (
    id INTEGER PRIMARY KEY AUTOINCREMENT, 
    name TEXT NOT NULL, 
    size INTEGER NOT NULL, 
    body BLOB NOT NULL
)
"#;

pub async fn init() -> Result<(), SqlxErr> {
    get_pool().execute(CREATE_SQL).await?;

    Ok(())
}

pub async fn download(id: i64) -> Result<(Vec<u8>, String), AppErr> {
    let row = sqlx::query(r#"SELECT name, body FROM tb_file WHERE id = ? LIMIT 1"#)
        .bind(id)
        .fetch_optional(get_pool())
        .await?
        .ok_or(new_err("没有找到该文件"))?;
    let body: Vec<u8> = row.get(0);
    let name: String = row.get(1);
    Ok((body, name))
}

pub async fn create(name: &str, body: &[u8]) -> Result<i64, AppErr> {
    let ret = sqlx::query(
        r#"
        INSERT INTO tb_file(name, size, body) VALUES (?, ?, ?)
    "#,
    )
    .bind(name)
    .bind(body.len() as i64)
    .bind(body)
    .execute(get_pool())
    .await?;
    Ok(ret.last_insert_rowid())
}

pub async fn del(id: i64) -> Result<(), AppErr> {
    sqlx::query(r#"DELETE FROM tb_file WHERE id = ?"#)
        .bind(id)
        .execute(get_pool())
        .await?;
    Ok(())
}

#[derive(Debug, Deserialize)]
pub struct FileInfo {
    pub id: i64,
    pub name: String,
    pub size: i64,
}

pub async fn query() -> Result<Box<[FileInfo]>, AppErr> {

    let rows = sqlx::query(r#"SELECT id, name, size FROM tb_file"#)
    .fetch_all( get_pool() )
    .await?;

    let mut vec = Vec::with_capacity(rows.len());
    for row in rows {
        let info = FileInfo {
            id: row.get(0),
            name: row.get(1),
            size: row.get(2)
        };
        vec.push(info);
    }
    Ok(vec.into_boxed_slice())
}

